package com.raja.servingStation.dao;

import com.raja.servingStation.common.constant.Constants;
import com.raja.servingStation.common.constant.MysqlPageVo;
import com.raja.servingStation.datasources.RepairSql2oTemp;
import com.raja.servingStation.datasources.Sql2oConnection;
import com.raja.servingStation.entity.ComponentModel;
import com.raja.servingStation.entity.FaultDiacrisis;
import com.raja.servingStation.entity.RepairOrder;
import com.raja.servingStation.entity.RepairPlan;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.sql2o.Connection;
import org.sql2o.Query;

import java.math.BigDecimal;
import java.util.List;


/**
 * Created by liuyanjun
 * on 2017/4/20.
 */
@Repository
public class TbOrderDao {
    private static final Logger log = LoggerFactory.getLogger(TbOrderDao.class);

    @Autowired
    private RepairSql2oTemp sql2oTemp;

    public String selectMaxId() throws Exception {
        String sql = "select max(order_code) as max_id from t_repair_order t";
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = Sql2oConnection.createQuery(con,sql);
            return query.executeScalar().toString();
        }
    }
    public String selectFirstOrderMaxId() throws Exception {
        String sql = "select max(order_code) as max_id from t_first_insurance_order t";
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = Sql2oConnection.createQuery(con,sql);
            return query.executeScalar().toString();
        }
    }
    public Double queryOrderScore(Long id) throws Exception{
        String sql = "SELECT " +
                " FORMAT((a + b + c) / 3, 1) sorce " +
                "FROM " +
                " ( " +
                "  SELECT " +
                "   sum(aa) a, " +
                "   sum(bb) b, " +
                "   sum(cc) c " +
                "  FROM " +
                "   ( " +
                "    SELECT " +
                "     tro.REPAIR_ID, " +
                "     IfNULL( " +
                "      tro.REAPIR_SPEED_EVALUATE, " +
                "      0 " +
                "     ) aa, " +
                "     IfNULL(tro.ARRIVE_TIME_EVALUATE, 0) bb, " +
                "     IfNULL(tro.SERVICE_EVALUATE, 0) cc " +
                "    FROM " +
                "     t_repair_order tro " +
                "    WHERE " +
                "     tro.REPAIR_ID = :id " +
                "   ) aa " +
                "  WHERE " +
                "   1 = 1 " +
                "  GROUP BY " +
                "   aa.REPAIR_ID " +
                " ) cc";
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = Sql2oConnection.createQuery(con,sql);
            query.addParameter("id",id);
            return Double.valueOf((String) query.executeScalar());
        }
    }

    public int queryDaiBanOrderCount(Integer servingStationId,String keyWord) throws Exception {
        String sql = "select SUM(id) from ( " +
                "select count(tro.ORDER_ID) id from t_repair_order tro where tro.ORDER_STATE in (1,4) and tro.REPAIR_ID = :servingStationId  " ;
                if(!StringUtils.isEmpty(keyWord)){
                    sql = sql + " and  INStr(tro.ORDER_CODE ,:keyWord) > 0  ";
                }
                sql = sql + "UNION ALL " +
                "SELECT count(tfio.fio_id) id from t_first_insurance_order tfio where tfio.INSURANCE_STATE = 0 and  tfio.REPAIR_ID = :servingStationId " ;
                if(!StringUtils.isEmpty(keyWord)){
                    sql = sql + " and   INStr(tfio.ORDER_CODE ,:keyWord) > 0   ";
                }
                sql = sql + ")aa ";
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = Sql2oConnection.createQuery(con,sql);
            query.addParameter("servingStationId",servingStationId);
            if(!StringUtils.isEmpty(keyWord)){
                query.addParameter("keyWord",keyWord);
            }
            return ((BigDecimal) query.executeScalar()).intValue();
        }
    }

    public List<RepairOrder> queryDaiBanOrder(MysqlPageVo mysqlPageVo, Integer userId, String keyWord) throws Exception {
        String sql = "select * from ( " +
                "SELECT " +
                " tro.ORDER_ID,tro.ORDER_CODE,tro.CAR_NO, tc.CAR_MODEL, " +
                "  tc.COMPANY_NAME,tro.DRIVER_PHONE,tro.APPOINTMENT_TIME, " +
                "  tro.ORDER_STATE, 1 ordertype ,tro.CREATE_TIME " +
                "FROM " +
                " t_repair_order tro ,t_car tc " +
                "WHERE  tro.car_id = tc.id and tro.ORDER_STATE in (1,4) and  " +
                " tro.REPAIR_ID = :userId   " ;
                if(!StringUtils.isEmpty(keyWord)){
                    sql = sql + " and  INStr(tro.ORDER_CODE ,:keyWord) > 0 ";
                }
                sql = sql +    "UNION ALL  " +
                        "SELECT " +
                        "  tf.FIO_ID,tf.ORDER_CODE,tf.CAR_NO,tc.CAR_MODEL,tf.CAR_OWNER, " +
                        "  tf.USER_PHONE,tf.FIRST_INSURANCE_DATE,tf.INSURANCE_STATE , " +
                        "   2 ordertype ,tf.CREATE_TIME " +
                        "from  " +
                        "  t_first_insurance_order tf  ,t_car tc " +
                        "WHERE  tf.car_id = tc.id  and tf.INSURANCE_STATE = 0 and tf.REPAIR_ID = :userId  " ;
                if(!StringUtils.isEmpty(keyWord)){
                    sql = sql + " and    INStr(tf.ORDER_CODE ,:keyWord) > 0   ";
                }
                sql = sql +    ") aa  ORDER BY aa.CREATE_TIME desc  " ;
        sql = Constants.mysql_page_pre_sql + sql + Constants.mysql_page_end_sql;
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = Sql2oConnection.createQuery(con,sql);
            query.addParameter("userId",userId);
            query.addParameter("limit",mysqlPageVo.getPageSize());
            query.addParameter("offset",mysqlPageVo.getOffset());
            if(!StringUtils.isEmpty(keyWord)){
                query.addParameter("keyWord",keyWord);
            }
            return  query.setAutoDeriveColumnNames(true).executeAndFetch(RepairOrder.class);
        }
    }

    public FaultDiacrisis getFaultDiacrisisByOrderId(Integer orderId) throws Exception {
        String sql = " select ORDER_ID, ORDER_CODE, BLAME_TYPE, FAULT_ADDRESS, DIACRISIS_TIME, BLAME_TYPE_SOURCE from" +
                "  t_fault_diacrisis  where ORDER_ID = :orderId" +
                "        ";
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = Sql2oConnection.createQuery(con,sql);
            query.addParameter("orderId",orderId);
            return  query.setAutoDeriveColumnNames(true).executeAndFetchFirst(FaultDiacrisis.class);
        }
    }

    public List<RepairPlan> getRepairPlansByOrderId(Integer orderId) throws Exception {
        String sql = " select  PP_ID , " +
                " ORDER_ID , " +
                " ORDER_CODE , " +
                " FK_ID , " +
                " COMPONENT_DEC , " +
                " COMPONENT_NUMBER , " +
                " COMPONENT_PRICE , " +
                " REPAIR_ACTION , " +
                " PRICE_TYPE , " +
                " CREATE_TIME   from" +
                "  t_repair_plan  where ORDER_ID = :orderId" +
                "        ";
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = Sql2oConnection.createQuery(con,sql);
            query.addParameter("orderId",orderId);
            return  query.setAutoDeriveColumnNames(true).executeAndFetch(RepairPlan.class);
        }
    }

    public List<ComponentModel> getCompontsByOrderId(Integer orderId) throws Exception {
        String sql = "SELECT  " +
                "  trc.ORDER_ID,tom.DP_ID,tom.GUARANTEE_MONTH,tom.GUARANTEE_MILEAGE,trc.CURRENT_TOTAL_KM,tc.KP_TIME  " +
                "FROM  " +
                "  t_repair_order trc,  " +
                "  t_component_model tom,  " +
                "  t_car tc,  " +
                "  t_repair_plan trp  " +
                "WHERE  " +
                "  trc.CAR_ID = tc.ID  " +
                "AND trc.ORDER_ID = trp.ORDER_ID  " +
                "AND trp.FK_ID = tom.DP_ID and trc.ORDER_ID= :orderId";
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = Sql2oConnection.createQuery(con,sql);
            query.addParameter("orderId",orderId);
            return  query.setAutoDeriveColumnNames(true).executeAndFetch(ComponentModel.class);
        }
    }

    public int queryOrderIsExit(Integer orderId, Integer orderType) throws Exception {
        String sql = "";
        if(orderType == 1){
            sql = "select count(*) from t_repair_order where order_id = :orderId";
        }
        if(orderType == 2){
            sql = "select count(*) from t_first_insurance_order where fio_id = :orderId";
        }
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = Sql2oConnection.createQuery(con,sql);
            query.addParameter("orderId",orderId);
            return  ((Long)query.executeScalar()).intValue();
        }
    }
}
